package com.bwda.dsrs.filecenter.util;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.bwda.dsrs.base.util.DateUtil;

import java.io.*;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

/**
 * excel 工具类
 *
 * @author liangjianyong
 * @date 2019/4/17
 */
public class ExcelUtil {

    private static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    private static final String XLS_STR="xls";

    private static final String XLSX_STR="xlsx";

    /**
     * 文本
     */
    public final static int CELL_TYPE_TEXT = 1;

    /**
     * 数值
     */
    public final static int CELL_TYPE_NUMBER = 2;

    /**
     * 数值
     */
    public final static int CELL_TYPE_COMMA_NUMBER = 3;

    /**
     * 货币
     */
    public final static int CELL_TYPE_MONEY = 4;

    /**
     * 文件绝对路径(含文件名)
     */
    private String filePath = "";

    /**
     * 工作簿对象
     */
    private Workbook workBook = null;

    /**
     * 解除科学计数法
     */
    private DecimalFormat decimalFormat = new DecimalFormat();

    /**
     * 文件名后缀
     */
    private String fileNameSuffix = "";

    /**
     * 单元格类型
     */
    private List<Integer> listCellType = new ArrayList<>();

    public ExcelUtil() {

    }

    public ExcelUtil(String filePath) {
        this.filePath = filePath;
    }

    /**
     * 通过文件初始化
     */
    public final void initByFile() {
        this.fileNameSuffix = StringUtils.substringAfterLast(filePath, ".");
        decimalFormat.setMaximumFractionDigits(8);
        InputStream inputStream = null;
        try {
            inputStream = new FileInputStream(filePath);
            workBook = WorkbookFactory.create(inputStream);
        }
        catch (Exception e) {
            try {
                inputStream = new FileInputStream(filePath);
                if (StringUtils.equals(fileNameSuffix, XLS_STR)) {
                    workBook = new HSSFWorkbook(inputStream);
                }
                else if (StringUtils.equals(fileNameSuffix, XLSX_STR)) {
                    workBook = new XSSFWorkbook(inputStream);
                }
            }
            catch (Exception ex) {
                logger.error("{}", ex.toString());
                workBook = null;
            }
        }
        finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                }
                catch (IOException e) {
                    logger.error("{}", e.toString());
                }
                finally {
                    inputStream = null;
                }
            }
        }
    }

    /**
     * 释放对象
     */
    public final void destroy() {
        workBook = null;
        filePath = null;
        decimalFormat = null;
    }

    /**
     * 读取工作簿中的指定序号的表格到List数组中
     *
     * @param intSheetNum Sheet的序号
     * @return return list<list<String>>
     */
    public final List<List<String>> readExcelAsList(int intSheetNum) {
        List<List<String>> listListString = null;
        if (workBook == null) {
            return null;
        }
        Sheet sheet = workBook.getSheetAt(intSheetNum);
        listListString = readExcelAsList(sheet);
        return listListString;
    }

    /**
     * 读取工作簿中的指定序号的表格到List数组中
     *
     * @param strSheetName Sheet的名称
     * @return return list<list<String>>
     */
    public final List<List<String>> readExcelAsList(String strSheetName) {
        List<List<String>> listListString = null;
        if (workBook == null) {
            return null;
        }
        Sheet sheet = workBook.getSheet(strSheetName);
        listListString = readExcelAsList(sheet);
        return listListString;
    }

    /**
     * 读取Sheet数据到List中
     *
     * @param sheet
     * @return list<list                               <                               String>>
     */
    public final List<List<String>> readExcelAsList(Sheet sheet) {
        List<List<String>> listListString = null;
        if (sheet.getPhysicalNumberOfRows() <= 0) {
            return null;
        }
        listListString = new ArrayList<List<String>>();
        List<String> listString = null;
        String strCellData = "";
        int intLastCellNum = 0;
        Cell cell = null;
        int i = 0;
        for (Row row : sheet) {
            intLastCellNum = row.getLastCellNum();
            if (intLastCellNum < 0) {
                continue;
            }
            listString = new ArrayList<String>(intLastCellNum);
            for (i = 0; i < intLastCellNum; i++) {
                strCellData = "";
                cell = row.getCell(i);
                if (cell != null) {
                    switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            strCellData = cell.getRichStringCellValue().getString();
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            strCellData = parseDateCell(cell);
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            strCellData = String.valueOf(cell.getBooleanCellValue());
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            strCellData = String.valueOf(cell.getNumericCellValue());
                            break;
                        default:
                            strCellData = "";
                    }
                }
                listString.add(strCellData);
            }
            if (!listString.isEmpty()) {
                listListString.add(listString);
            }
        }
        return listListString;
    }

    public final List<List<String>> readExcelAsList(int intSheetNum, int startRow, int endRow) {
        List<List<String>> listListString = null;
        if (workBook == null) {
            return null;
        }
        Sheet sheet = workBook.getSheetAt(intSheetNum);
        if (sheet.getPhysicalNumberOfRows() <= 0) {
            return null;
        }
        listListString = new ArrayList<List<String>>();
        List<String> listString = null;
        String strCellData = "";
        int intLastCellNum = 0;
        Cell cell = null;
        int i = 0;
        int rowIndex = 0;
        for (Row row : sheet) {
            rowIndex++;
            if (rowIndex < startRow) {
                continue;
            }
            if (rowIndex > endRow) {
                break;
            }
            intLastCellNum = row.getLastCellNum();
            if (intLastCellNum < 0) {
                continue;
            }
            listString = new ArrayList<String>(intLastCellNum);
            for (i = 0; i < intLastCellNum; i++) {
                strCellData = "";
                cell = row.getCell(i);
                if (cell != null) {
                    switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            strCellData = cell.getRichStringCellValue().getString();
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            strCellData = parseDateCell(cell);
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            strCellData = String.valueOf(cell.getBooleanCellValue());
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            strCellData = String.valueOf(cell.getNumericCellValue());
                            break;
                        default:
                            strCellData = "";
                    }
                }
                listString.add(strCellData);
            }
            if (!listString.isEmpty()) {
                listListString.add(listString);
            }
        }
        return listListString;
    }

    private final String parseDateCell(Cell cell) {
        String strCellData = "";
        Calendar calendar = Calendar.getInstance();
        if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
            calendar.setTime(cell.getDateCellValue());
            if (calendar.get(Calendar.HOUR) == 0 && calendar.get(Calendar.MINUTE) == 0
                && calendar.get(Calendar.SECOND) == 0) {
                strCellData = DateUtil.formatDate(calendar.getTime(), "yyyy-MM-dd");
            }
            else {
                strCellData = DateUtil.formatDate(calendar.getTime(), "yyyy-MM-dd HH:mm:ss");
            }
        }
        else {
            strCellData = decimalFormat.format(cell.getNumericCellValue());
            strCellData = strCellData.replaceAll(",", "");
        }
        return strCellData;
    }

    /**
     * 获得：文件绝对路径(含文件名)
     *
     * @return the fileRemotePath
     */

    public final String getFilePath() {
        return filePath;
    }

    /**
     * 设置：文件绝对路径(含文件名)
     *
     * @param filePath the fileRemotePath to set
     */

    public final void setFilePath(String filePath) {
        this.filePath = filePath;
    }

    /**
     * 获得：工作簿对象
     *
     * @return the workbook
     */

    public final Workbook getWorkbook() {
        return workBook;
    }

}
